package repositories;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.lang3.StringEscapeUtils;

public abstract class BaseRepository {

	private final String CONNECTION_PASSWORD = "uzJdVluivFPWasf020lT";
	private final String CONNECTION_USERNAME = "usr_journal";
	private final String CONNECTION_HOSTNAME = "jdbc:mysql://localhost/journal_aggregator";
	private final String CONNECTION_CLASSTYPE = "com.mysql.jdbc.Driver";

	protected Connection getConnection() throws SQLException {
		Connection conn = null;

		try {

			Class.forName(CONNECTION_CLASSTYPE).newInstance();
			conn = DriverManager.getConnection(CONNECTION_HOSTNAME,
					CONNECTION_USERNAME, CONNECTION_PASSWORD);
		} catch (ClassNotFoundException ex) {
			System.err.append(String.format(
					"Unable to find JDBC class: %s\n\n", CONNECTION_CLASSTYPE));
		} catch (InstantiationException ex) {
			System.err.append(String.format(
					"Unable to instantiate JDBC class: %s\n\n",
					CONNECTION_CLASSTYPE));
		} catch (IllegalAccessException ex) {
			System.err.append("Unable to access database.");
		}
		return conn;
	}

	protected int executeUpdate(String sql) {
		int result = -1;
		Connection conn = null;

		try {
			conn = getConnection();
			Statement stmt = conn.createStatement();

			result = stmt.executeUpdate(sql);
		} catch (SQLException ex) {
			System.err.append(String.format("%s\nSQL: %s\n\n", ex.getMessage(),
					sql));
			ex.printStackTrace();
		} finally {
			closeConnection(conn);
		}

		return result;
	}

	protected ResultSet executeQuery(Connection conn, String sql) {
		ResultSet result = null;

		try {
			conn = getConnection();
			Statement stmt = conn.createStatement();

			result = stmt.executeQuery(sql);
		} catch (SQLException ex) {
			System.err.append(String.format("%s\nSQL: %s\n\n", ex.getMessage(),
					sql));
			ex.printStackTrace();
		}

		return result;
	}

	protected void closeConnection(Connection conn) {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
				conn = null;
			}
		} catch (Exception ex) {
			// Do nothing, it's closed
		}
	}

	protected String cleanString(String value) {
		if (value == null)
			return null;
		value = StringEscapeUtils.unescapeHtml4(value);
		return value.replace("'", "\\'");
	}
}
